import pandas as pd
import pymysql
import sqlalchemy
from sqlalchemy import create_engine

# db = pymysql.connect(host='localhost', user='root', password='sqlpass', port=3306, db='tradedata')
# cursor = db.cursor()


def create_spot_table(sy):
    db = pymysql.connect(host='localhost', user='root', password='sqlpass', port=3306, db='tradedata')
    cursor = db.cursor()
    tablename = 'spot_{}'.format(sy.lower())
    sql = "CREATE TABLE %s" % tablename + "(`date` VARCHAR(40) PRIMARY KEY, `name` VARCHAR(40) NOT NULL, " \
                                          "`spot` DOUBLE, `main_code` VARCHAR(40), " \
                                          "`main_price` DOUBLE, `basis` DOUBLE, " \
                                          "`exchange` VARCHAR(40))ENGINE=innodb DEFAULT CHARSET=utf8mb4"
    try:
        cursor.execute(sql)
    except:
        print("Table %s already exists..." % tablename)
    else:
        pass
    cursor.close()
    db.close()


def create_fut_table(tc):
    db = pymysql.connect(host='localhost', user='root', password='sqlpass', port=3306, db='tradedata')
    cursor = db.cursor()
    tablename = 'fut_{}'.format(tc.lower())
    sql = 'CREATE TABLE %s' % tablename + "(`trade_date` VARCHAR(40) PRIMARY KEY, " \
                                          "`ts_code` VARCHAR(40) NOT NULL, " \
                                          "`open` DOUBLE, " \
                                          "`high` DOUBLE, " \
                                          "`low` DOUBLE, " \
                                          "`close` DOUBLE, " \
                                          "`vol` INT," \
                                          "`oi` INT," \
                                          "`oi_chg` INT," \
                                          "`informed` DOUBLE," \
                                          "`uninformed` DOUBLE," \
                                          "`lsdexlite` DOUBLE" \
                                          ")ENGINE=innodb DEFAULT CHARSET=utf8mb4"
    try:
        cursor.execute(sql)
    except:
        print("Table %s already exists..." % tablename)
    else:
        pass
    cursor.close()
    db.close()


def create_indice_table(tc):
    db = pymysql.connect(host='localhost', user='root', password='sqlpass', port=3306, db='tradedata')
    cursor = db.cursor()
    tablename = 'index_{}'.format(tc.lower())
    sql = 'CREATE TABLE %s' % tablename + "(`trade_date` VARCHAR(40) PRIMARY KEY, " \
                                          "`ts_code` VARCHAR(40) NOT NULL, " \
                                          "`close` DOUBLE, " \
                                          "`open` DOUBLE, " \
                                          "`high` DOUBLE, " \
                                          "`low` DOUBLE, " \
                                          "`vol` INT,"\
                                          "`amount` INT"\
                                          ")ENGINE=innodb DEFAULT CHARSET=utf8mb4"
    try:
        cursor.execute(sql)
    except:
        print("Table %s already exists..." % tablename)
    else:
        pass
    cursor.close()
    db.close()


def create_spdrdex_table(sy):
    db = pymysql.connect(host='localhost', user='root', password='sqlpass', port=3306, db='tradedata')
    cursor = db.cursor()
    tablename = 'spdrdex_{}'.format(sy.lower())
    sql = 'CREATE TABLE %s' % tablename + "(`trade_date` VARCHAR(20) PRIMARY KEY, " \
                                          "`symbol` VARCHAR(20) NOT NULL, " \
                                          "`informed` DOUBLE, " \
                                          "`uninformed` DOUBLE, " \
                                          "`lsdexlite` DOUBLE " \
                                          ")ENGINE=innodb DEFAULT CHARSET=utf8mb4"
    try:
        cursor.execute(sql)
    except:
        print("Table %s already exists..." % tablename)
    else:
        pass
    cursor.close()
    db.close()


def create_fut_index_table(sy):
    db = pymysql.connect(host='localhost', user='root', password='sqlpass', port=3306, db='tradedata')
    cursor = db.cursor()
    tablename = 'fut_index_{}'.format(sy.lower())
    sql = 'CREATE TABLE %s' % tablename + "(`date` VARCHAR(20) PRIMARY KEY, " \
                                          "`close` DOUBLE," \
                                          "`volume` INT," \
                                          "`open_interest` INT" \
                                          ")ENGINE=innodb DEFAULT CHARSET=utf8mb4"
    try:
        cursor.execute(sql)
    except:
        print("Table %s already exists..." % tablename)
    else:
        pass
    cursor.close()
    db.close()


def create_ry_table(sy):
    db = pymysql.connect(host='localhost', user='root', password='sqlpass', port=3306, db='tradedata')
    cursor = db.cursor()
    tablename = 'roll_yield_{}'.format(sy.lower())
    sql = 'CREATE TABLE %s' % tablename + "(`index` VARCHAR(20) PRIMARY KEY, " \
                                          "`roll_yield` DOUBLE," \
                                          "`near_by` VARCHAR(20), " \
                                          "`deferred` VARCHAR(20)" \
                                          ")ENGINE=innodb DEFAULT CHARSET=utf8mb4"
    try:
        cursor.execute(sql)
    except:
        print("Table %s already exists..." % tablename)
    else:
        pass
    cursor.close()
    db.close()


# ['trade_date', 'symbol', 'informed', 'uninformed', 'lsdexlite']

def spot_to_sql(df, sy):
    '''创建给定名称的sql table,分品种写入现货数据'''
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    df.to_sql(name='spot_{}'.format(sy.lower()), con=engine, if_exists='append')

def future_to_sql(df, tc):
    '''创建名称为交易合约的sql table,然后写入期货合约数据'''
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    df.to_sql(name='fut_{}'.format(tc.lower()), con=engine, if_exists='append')

def indice_to_sql(df, tc):
    '''创建名称为交易合约的sql table,然后写入指数合约数据'''
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    df.to_sql(name='index_{}'.format(tc.lower()), con=engine, if_exists='append')

def spdrdx_to_sql(df, sy):
    '''创建给定名称的sql table,分品种写入现货数据'''
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    df.to_sql(name='spdrdex_{}'.format(sy.lower()), con=engine, if_exists='append')


def fut_index_to_sql(df, sy):
    '''创建给定名称的sql table,分品种写入现货数据'''
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    df.to_sql(name='fut_index_{}'.format(sy.lower()), con=engine, if_exists='append')


def ry_to_sql(df, sy):
    '''创建给定名称的sql table,分品种写入现货数据'''
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    df.to_sql(name='roll_yield_{}'.format(sy.lower()), con=engine, if_exists='append')


'''------创建、写入部分的函数与读取部分的函数的分界线------'''


# 这里定义的读取sql的函数，默认是读取整张表，原因在于如果提取特定列的数据，则也需要同时指定提取date列，否则不能将其作为索引
# 提取出来之后的结果可以通过DataFrame方式去做提取，也很方便，所以在此直接用read_sql_table提取整张表。
def read_sql_spot(tablename):
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    result = pd.read_sql_table(tablename, con=engine, index_col='date')
    return result


def read_sql_fut(tablename):
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    result = pd.read_sql_table(tablename, con=engine, index_col='trade_date')
    return result


def read_sql_indice(tablename):
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    result = pd.read_sql_table(tablename, con=engine, index_col='trade_date')
    return result


def read_sql_spdrdx(tablename):
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    result = pd.read_sql_table(tablename, con=engine, index_col='trade_date')
    return result


def read_sql_fut_index(tablename):
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    result = pd.read_sql_table(tablename, con=engine, index_col='date')
    return result


def read_sql_ry(tablename):
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    result = pd.read_sql_table(tablename, con=engine, index_col='index')
    return result

# r = read_sql_spot('spot_m_2019')
# print(r.head())
# print(r['spot']['2019-01-07'])
# print(r[['spot', 'name']])
# print(r.loc['2019-01-01':'2019-01-07', ['name', 'spot']])
# 以上是从得到的表中提取出想要的数据的一些用法

